﻿using Newtonsoft.Json.Linq;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace YB_Server_Windows
{
    public class YbMakeExcel
    {
        private ICellStyle cellStyle;
        private ICellStyle cellStyleCaption;
        public class CaptionData
        {
            public string Filed;
            public string Caption;
            public string Kind;
        }
        public class SheetData
        {
            public string name;
            public ISheet sheet;
            public List<CaptionData> caption;
            public int SheetCount = 0;
        }
        private IWorkbook excel;
        private string fileName;
        private SheetData defSheet;
        private Dictionary<string, SheetData> Sheet = new Dictionary<string, SheetData>();
        public void SetFileName(string name)
        {
            fileName = name;
        }
        public void AddSheet(string name)//添加一个Sheet
        {
            int index = excel.GetSheetIndex(name);
            if (index == -1)
            {
                ISheet sheet = excel.CreateSheet(name);
                sheet.AutoSizeColumn(0);
                SheetData sheetData = new SheetData();
                sheetData.sheet = sheet;
                sheetData.name = name;
                Sheet.Add(name, sheetData);
                defSheet = sheetData;
            }
        }
        public void SelectSheet(string name)//选择当前工作的Sheet
        {
            if (Sheet.ContainsKey(name))
            {
                defSheet = Sheet[name];
            }
        }
        public void SetCaption(List<CaptionData> caption)
        {
            if (defSheet != null)
            {
                defSheet.caption = caption;
                //创建第一列
                IRow row = defSheet.sheet.CreateRow(0);
                for (int i = 0; i < caption.Count; i++)
                {
                    ICell cell = row.CreateCell(i);
                    cell.CellStyle = cellStyleCaption;
                    cell.SetCellType(CellType.String);
                    cell.SetCellValue(caption[i].Caption);
                }
                //第一行冻结
                defSheet.sheet.CreateFreezePane(0, 1);
                defSheet.SheetCount++;
            }
        }
        public void MakeExcelFile(FileStream file)
        {
            excel.Write(file);
        }
        public void AddRow(JArray data)
        {
            if (defSheet != null)
            {
                for (int i = 0; i < data.Count(); i++)
                {
                    JToken row = data[i];
                    int rowindex = defSheet.SheetCount;
                    IRow sheetrow = defSheet.sheet.CreateRow(rowindex);
                    for (int l = 0; l < defSheet.caption.Count; l++)
                    {
                        ICell cell = sheetrow.CreateCell(l);
                        cell.CellStyle = cellStyle;
                        CaptionData item = defSheet.caption[l];
                        
                        if (row[item.Filed]!=null)
                        {
                            switch (item.Kind)
                            {
                                case "time":
                                    ICellStyle dateStyle = excel.CreateCellStyle();
                                    dateStyle.DataFormat = excel.GetCreationHelper().CreateDataFormat().GetFormat("yyyy-mm-dd hh:mm:ss");
                                    DateTime dateTime = DateTime.ParseExact(row[item.Filed].ToString(), "yyyy-MM-dd HH:mm:ss", CultureInfo.InvariantCulture);
                                    double excelDate = dateTime.Date.ToOADate() + (dateTime - dateTime.Date).TotalHours / 24;
                                    cell.SetCellType(CellType.Numeric);
                                    cell.SetCellValue(excelDate);
                                    cell.CellStyle = dateStyle;
                                    break;
                                case "string":
                                    cell.SetCellType(CellType.String);
                                    cell.SetCellValue(row[item.Filed].ToString());
                                    break;
                                case "number":
                                    cell.SetCellType(CellType.Numeric);
                                    cell.SetCellValue(Math.Round(row[item.Filed].ToObject<float>(), 2));
                                    break;
                                default:
                                    cell.SetCellType(CellType.String);
                                    cell.SetCellValue(row[item.Filed].ToString());
                                    break;
                            }
                        }
                        else
                        {
                            cell.SetCellType(CellType.String);
                            cell.SetCellValue("-");
                        }
                    }
                    defSheet.SheetCount++;
                }
            }

        }
        public YbMakeExcel()
        {
            excel = new XSSFWorkbook();
            cellStyle = excel.CreateCellStyle();
            cellStyle.BorderTop = BorderStyle.Thin;
            cellStyle.BorderRight = BorderStyle.Thin;
            cellStyle.BorderLeft = BorderStyle.Thin;
            cellStyle.BorderBottom = BorderStyle.Thin;
            cellStyleCaption = excel.CreateCellStyle();
            cellStyleCaption.BorderTop = BorderStyle.Medium;
            cellStyleCaption.BorderRight = BorderStyle.Medium;
            cellStyleCaption.BorderLeft = BorderStyle.Medium;
            cellStyleCaption.BorderBottom = BorderStyle.Medium;

        }
    }
}
